{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f16967ef",
   "metadata": {},
   "source": [
    "# Deploy PostgreSQL with Helm in TLS Mode"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1247e2e7-706c-44a3-a45c-fba638e50f31",
   "metadata": {},
   "source": [
    "### <font color=\"red\"> NOTE: This PostgreSQL setup guide is intended to demonstrate the capabilities of the Feast operator in configuring Feast with PostgreSQL in TLS mode. For ongoing assistance with Postgres setup, we recommend consulting the official Helm PostgreSQL documentation.</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cce2278a",
   "metadata": {},
   "source": [
    "## Step 1: Install Prerequisites"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e4102d8",
   "metadata": {},
   "source": [
    "Before starting, ensure you have the following installed:\n",
    "- `kubectl` (Kubernetes CLI)\n",
    "- `helm` (Helm CLI)\n",
    "- A Kubernetes cluster (e.g., Minikube, GKE, EKS, or AKS)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44b611ba-097e-4777-b77b-739116e7e4d6",
   "metadata": {},
   "source": [
    "**Note:** When deploying PostgreSQL and Feast on a Kubernetes cluster, it's important to ensure that your cluster has sufficient resources to support both applications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e2b40efc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Client Version: v1.31.2\n",
      "Kustomize Version: v5.4.2\n",
      "version.BuildInfo{Version:\"v3.17.0\", GitCommit:\"301108edc7ac2a8ba79e4ebf5701b0b6ce6a31e4\", GitTreeState:\"clean\", GoVersion:\"go1.23.4\"}\n"
     ]
    }
   ],
   "source": [
    "# Verify kubectl and helm are installed\n",
    "!kubectl version --client\n",
    "!helm version"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b72fabe",
   "metadata": {},
   "source": [
    "## Step 2: Add the Bitnami Helm Repository"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f439691e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"bitnami\" already exists with the same configuration, skipping\n",
      "Hang tight while we grab the latest from your chart repositories...\n",
      "...Successfully got an update from the \"bitnami\" chart repository\n",
      "Update Complete. ⎈Happy Helming!⎈\n"
     ]
    }
   ],
   "source": [
    "# Add the Bitnami Helm repository\n",
    "!helm repo add bitnami https://charts.bitnami.com/bitnami\n",
    "!helm repo update"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f51e5c8-41ba-417e-a2fc-78cf5951d9dc",
   "metadata": {},
   "source": [
    "## Step 3: create kubernetes feast namespace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d114872a-7a43-4eca-8748-6dc7346dc176",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "namespace/feast created\n",
      "Context \"kind-kind\" modified.\n"
     ]
    }
   ],
   "source": [
    "!kubectl create ns feast\n",
    "!kubectl config set-context --current --namespace feast"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41f4e8db",
   "metadata": {},
   "source": [
    "## Step 4: Generate Self Signed TLS Certificates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c34957e4-dd7f-49c1-986c-eefe74dd7e22",
   "metadata": {},
   "source": [
    "**Note**: \n",
    "- Self signed certificates are used only for demo purpose, consider using a managed certificate service (e.g., Let's Encrypt) instead of self-signed certificates.\n",
    "- \"Replace the `CN` values in the certificate generation step with your actual domain names.\","
   ]
  },
  {
   "cell_type": "markdown",
   "id": "500f9010-6329-4868-83d5-9c063d5890f5",
   "metadata": {},
   "source": [
    "Delete the directory of existing certificates if you running this demo not first time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bdc71e19-0fcc-4a1f-ba94-8b5e427e45d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete certificates directory if you are running this example not first time.\n",
    "!rm -rf postgres-tls-certs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91dc26c9-cfaa-46f5-8252-7ad463264236",
   "metadata": {},
   "source": [
    "Generate the certificates by executing below scripts. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8e192410",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "..+.......+.........+...+.....+......+.......+...+.....+......+.+..+......+.+.....+...+.......+...+..+.+.....+.......+........+.......+......+...........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.+...+...+........+....+..+...+...+....+...+......+..+..........+..+...+...+...............+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*...+...........+......+..........+..+.+.....+....+......+.....................+...+...+..+...+.......+..+.........+.......+.....+....+........+.+..+.............+......+....................+.........+.+......+.....+.......+........+......................+......+..+...+....+...+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
      "..+...+......+.+.........+...+......+..+.......+.....+.+..+...+.+...+......+.....+.........+......+.+...........+....+..................+...+.........+...+.....+.+.....+...............+.+......+...+............+...+......+......+........+.+.....+.............+..+.+..+.+..............+...+...+....+............+...+.....+......+.+.....+.+...+..+...+...................+...........+....+..+.................................+..........+...........+......+.+...+..+...+.......+.....+.......+...........+.......+...+......+.....+..........+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
      "-----\n",
      ".+....+......+..+....+...+.....+......+.+........+..........+.....+............+.+...+..+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*...+.+..............+...............+.+...........+.......+...+..+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....+...............+............+.....+.+......+........+...+...+.+...+.....+......+.+..............+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
      "............+....+.....+.+...+........+..........+..............+.+..............+.........+.+...+...........+......+......+.......+........+...+.........+.+.....+.+.....+.+........+.+.....................+..+.............+........+......+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
      "-----\n",
      "Certificate request self-signature ok\n",
      "subject=CN = postgresql.feast.svc.cluster.local\n",
      "..+....+...+.....+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....+.+..+.......+......+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.+..+.+.....+.+...+..................+.....+...+...................+......+..+...+.+......+..+..........+..+..................+.+..+...+......+.+............+..+....+...........+..........+.....+...+......+.+...+...+..+......+.+...+...+.........+......+.....+..................+.+.....+....+..............+.+..............+.+......+....................+..........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n",
      "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.....+.........+...+..+.......+.....+.+..+.+......+....................+......+.............+......+...+..+...+.+..+...+....+.....+...+...+.........+......+.+.....+.+..+..........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*..+...+.+...........+....+.....+...................+..+.+..+......+............+..........+.........+...+..+...............+..........+.....+....+............+........+.+........+.+.....+.......+.....++\n",
      "-----\n",
      "Certificate request self-signature ok\n",
      "subject=CN = admin\n"
     ]
    }
   ],
   "source": [
    "# Create a directory for certificates\n",
    "!mkdir -p postgres-tls-certs\n",
    "\n",
    "# Generate a CA certificate\n",
    "!openssl req -new -x509 -days 365 -nodes -out postgres-tls-certs/ca.crt -keyout postgres-tls-certs/ca.key -subj \"/CN=PostgreSQL CA\"\n",
    "\n",
    "# Generate a server certificate\n",
    "!openssl req -new -nodes -out postgres-tls-certs/server.csr -keyout postgres-tls-certs/server.key -subj \"/CN=postgresql.feast.svc.cluster.local\"\n",
    "!openssl x509 -req -in postgres-tls-certs/server.csr -days 365 -CA postgres-tls-certs/ca.crt -CAkey postgres-tls-certs/ca.key -CAcreateserial -out postgres-tls-certs/server.crt\n",
    "\n",
    "# Generate a client certificate\n",
    "!openssl req -new -nodes -out postgres-tls-certs/client.csr -keyout postgres-tls-certs/client.key -subj \"/CN=admin\"\n",
    "!openssl x509 -req -in postgres-tls-certs/client.csr -days 365 -CA postgres-tls-certs/ca.crt -CAkey postgres-tls-certs/ca.key -CAcreateserial -out postgres-tls-certs/client.crt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e39cb28",
   "metadata": {},
   "source": [
    "## Step 5: Create Kubernetes Secrets for Certificates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4775780-3734-40ba-ae43-48f1e47b481a",
   "metadata": {},
   "source": [
    "In this step, we will create **two Kubernetes secrets** that reference the certificates generated earlier step:\n",
    "\n",
    "- **`postgresql-server-certs`**  \n",
    "  This secret contains the server certificates and will be used by the PostgreSQL server.\n",
    "\n",
    "- **`postgresql-client-certs`**  \n",
    "  This secret contains the client certificates and will be used by the PostgreSQL client. In our case it will be feast application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d728d0d5-2ba6-4d4d-b4be-62fb020530d4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "secret/postgresql-server-certs created\n",
      "secret/postgresql-client-certs created\n"
     ]
    }
   ],
   "source": [
    "# Create a secret for the server certificates\n",
    "!kubectl create secret generic postgresql-server-certs   --from-file=ca.crt=./postgres-tls-certs/ca.crt   --from-file=tls.crt=./postgres-tls-certs/server.crt   --from-file=tls.key=./postgres-tls-certs/server.key\n",
    "\n",
    "# Create a secret for the client certificates\n",
    "!kubectl create secret generic postgresql-client-certs   --from-file=ca.crt=./postgres-tls-certs/ca.crt   --from-file=tls.crt=./postgres-tls-certs/client.crt   --from-file=tls.key=./postgres-tls-certs/client.key"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67d62692",
   "metadata": {},
   "source": [
    "## Step 6: Deploy PostgreSQL with Helm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e14cae77",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NAME: postgresql\n",
      "LAST DEPLOYED: Tue Feb 25 08:12:21 2025\n",
      "NAMESPACE: feast\n",
      "STATUS: deployed\n",
      "REVISION: 1\n",
      "TEST SUITE: None\n",
      "NOTES:\n",
      "CHART NAME: postgresql\n",
      "CHART VERSION: 16.4.9\n",
      "APP VERSION: 17.3.0\n",
      "\n",
      "Did you know there are enterprise versions of the Bitnami catalog? For enhanced secure software supply chain features, unlimited pulls from Docker, LTS support, or application customization, see Bitnami Premium or Tanzu Application Catalog. See https://www.arrow.com/globalecs/na/vendors/bitnami for more information.\n",
      "\n",
      "** Please be patient while the chart is being deployed **\n",
      "\n",
      "PostgreSQL can be accessed via port 5432 on the following DNS names from within your cluster:\n",
      "\n",
      "    postgresql.feast.svc.cluster.local - Read/Write connection\n",
      "\n",
      "To get the password for \"postgres\" run:\n",
      "\n",
      "    export POSTGRES_ADMIN_PASSWORD=$(kubectl get secret --namespace feast postgresql -o jsonpath=\"{.data.postgres-password}\" | base64 -d)\n",
      "\n",
      "To get the password for \"admin\" run:\n",
      "\n",
      "    export POSTGRES_PASSWORD=$(kubectl get secret --namespace feast postgresql -o jsonpath=\"{.data.password}\" | base64 -d)\n",
      "\n",
      "To connect to your database run the following command:\n",
      "\n",
      "    kubectl run postgresql-client --rm --tty -i --restart='Never' --namespace feast --image docker.io/bitnami/postgresql:17.3.0-debian-12-r1 --env=\"PGPASSWORD=$POSTGRES_PASSWORD\" \\\n",
      "      --command -- psql --host postgresql -U admin -d feast -p 5432\n",
      "\n",
      "    > NOTE: If you access the container using bash, make sure that you execute \"/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash\" in order to avoid the error \"psql: local user with ID 1001} does not exist\"\n",
      "\n",
      "To connect to your database from outside the cluster execute the following commands:\n",
      "\n",
      "    kubectl port-forward --namespace feast svc/postgresql 5432:5432 &\n",
      "    PGPASSWORD=\"$POSTGRES_PASSWORD\" psql --host 127.0.0.1 -U admin -d feast -p 5432\n",
      "\n",
      "WARNING: The configured password will be ignored on new installation in case when previous PostgreSQL release was deleted through the helm command. In that case, old PVC will have an old password, and setting it through helm won't take effect. Deleting persistent volumes (PVs) will solve the issue.\n",
      "\n",
      "WARNING: There are \"resources\" sections in the chart not set. Using \"resourcesPreset\" is not recommended for production. For production installations, please set the following values according to your workload needs:\n",
      "  - primary.resources\n",
      "  - readReplicas.resources\n",
      "  - volumePermissions.resources\n",
      "+info https://kubernetes.io/docs/concepts/configuration/manage-resources-containers/\n"
     ]
    }
   ],
   "source": [
    "# Helm values for TLS configuration\n",
    "helm_values = \"\"\"\n",
    "tls:\n",
    "  enabled: true\n",
    "  certificatesSecret: \"postgresql-server-certs\"\n",
    "  certFilename: \"tls.crt\"\n",
    "  certKeyFilename: \"tls.key\"\n",
    "  certCAFilename: \"ca.crt\"\n",
    "\n",
    "volumePermissions:\n",
    "  enabled: true\n",
    "\n",
    "# Set fixed PostgreSQL credentials\n",
    "\n",
    "global:\n",
    "  postgresql:\n",
    "    auth:\n",
    "      username: admin\n",
    "      password: password\n",
    "      database: feast\n",
    "\"\"\"\n",
    "\n",
    "# Write the values to a file\n",
    "with open(\"values.yaml\", \"w\") as f:\n",
    "    f.write(helm_values)\n",
    "\n",
    "# Install PostgreSQL with Helm\n",
    "!helm install postgresql bitnami/postgresql --version 16.4.9 -f values.yaml -n feast "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5be34ace",
   "metadata": {},
   "source": [
    "## Step 7: Verify the postgres Deployment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "132df785-762e-473a-90d2-5fdb66a59a97",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "pod/postgresql-0 condition met\n",
      "\n",
      "NAME           READY   STATUS    RESTARTS   AGE\n",
      "postgresql-0   1/1     Running   0          14s\n",
      "\n",
      "Defaulted container \"postgresql\" out of: postgresql, init-chmod-data (init)\n",
      "ssl = 'on'\n",
      "ssl_ca_file = '/opt/bitnami/postgresql/certs/ca.crt'\n",
      "ssl_cert_file = '/opt/bitnami/postgresql/certs/tls.crt'\n",
      "#ssl_crl_file = ''\n",
      "#ssl_crl_dir = ''\n",
      "ssl_key_file = '/opt/bitnami/postgresql/certs/tls.key'\n",
      "#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'\t# allowed SSL ciphers\n",
      "#ssl_prefer_server_ciphers = on\n",
      "#ssl_ecdh_curve = 'prime256v1'\n",
      "#ssl_min_protocol_version = 'TLSv1.2'\n",
      "#ssl_max_protocol_version = ''\n",
      "#ssl_dh_params_file = ''\n",
      "#ssl_passphrase_command = ''\n",
      "#ssl_passphrase_command_supports_reload = off\n",
      "\n",
      "Defaulted container \"postgresql\" out of: postgresql, init-chmod-data (init)\n",
      "                                                     List of databases\n",
      "   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   \n",
      "-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------\n",
      " feast     | admin    | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =Tc/admin            +\n",
      "           |          |          |                 |             |             |        |           | admin=CTc/admin\n",
      " postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | \n",
      " template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +\n",
      "           |          |          |                 |             |             |        |           | postgres=CTc/postgres\n",
      " template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +\n",
      "           |          |          |                 |             |             |        |           | postgres=CTc/postgres\n",
      "(4 rows)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Wait for the status of the PostgreSQL pod to be in Ready status.\n",
    "!kubectl wait --for=condition=Ready pod -l app.kubernetes.io/name=postgresql --timeout=60s\n",
    "\n",
    "# Insert an empty line in the output for verbocity.\n",
    "print()\n",
    "\n",
    "# display the pod status.\n",
    "!kubectl get pods -l app.kubernetes.io/name=postgresql\n",
    "\n",
    "# Insert an empty line in the output for verbocity.\n",
    "print()\n",
    "\n",
    "# check if the ssl is on and the path to certificates is configured.\n",
    "!kubectl exec postgresql-0 -- cat /opt/bitnami/postgresql/conf/postgresql.conf | grep ssl\n",
    "\n",
    "# Insert an empty line in the output for verbocity.\n",
    "print()\n",
    "\n",
    "# Connect to PostgreSQL using TLS (non-interactive mode)\n",
    "!kubectl exec postgresql-0 -- env PGPASSWORD=password psql -U admin -d feast -c '\\l'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c921423a-81df-456e-9cca-f689070c44d2",
   "metadata": {},
   "source": [
    "## Step 8: Port forwarding in the terminal for the connection testing using python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6a26bb4-e0e7-419e-9c91-f0d63db127bc",
   "metadata": {},
   "source": [
    "**Note:** If you do not intend to test the PostgreSQL connection from outside the Kubernetes cluster, you can skip the remaining steps."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fcad5e1-66d2-4353-aba7-3549ef21bc9f",
   "metadata": {},
   "source": [
    "**Note:**\n",
    "To test a connection to a PostgreSQL database outside of your Kubernetes cluster, you'll need to execute the following command in your system's terminal window. This is necessary because Jupyter Notebook does not support running commands in a separate thread."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88a4a7c1-51c4-4c5a-9472-5cace1c47a1c",
   "metadata": {},
   "source": [
    "kubectl port-forward svc/postgresql 5432:5432"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8777ca3-bf59-4f23-b7d0-60ae8c92d5a5",
   "metadata": {},
   "source": [
    "## Step 9: Check the connection using Python sql alchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5a523f9f-784f-493b-b69d-5a3cb1a830af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql+psycopg://admin:password@localhost:5432/feast?sslmode=verify-ca&sslrootcert=postgres-tls-certs/ca.crt&sslcert=postgres-tls-certs/client.crt&sslkey=postgres-tls-certs/client.key\n",
      "Connected successfully!\n"
     ]
    }
   ],
   "source": [
    "# Define database connection parameters\n",
    "DB_USER = \"admin\"\n",
    "DB_PASSWORD = \"password\"\n",
    "DB_HOST = \"localhost\"\n",
    "DB_PORT = \"5432\"\n",
    "DB_NAME = \"feast\"\n",
    "\n",
    "# TLS Certificate Paths\n",
    "SSL_CERT = \"postgres-tls-certs/client.crt\"\n",
    "SSL_KEY = \"postgres-tls-certs/client.key\"\n",
    "SSL_ROOT_CERT = \"postgres-tls-certs/ca.crt\"\n",
    "\n",
    "import os\n",
    "os.environ[\"FEAST_CA_CERT_FILE_PATH\"] = \"postgres-tls-certs/ca.crt\"\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "# Create SQLAlchemy connection string\n",
    "DATABASE_URL = (\n",
    "    f\"postgresql+psycopg://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}?\"\n",
    "    f\"sslmode=verify-ca&sslrootcert={SSL_ROOT_CERT}&sslcert={SSL_CERT}&sslkey={SSL_KEY}\"\n",
    ")\n",
    "\n",
    "print(DATABASE_URL)\n",
    "\n",
    "# Create SQLAlchemy engine\n",
    "engine = create_engine(DATABASE_URL)\n",
    "\n",
    "# Test connection\n",
    "try:\n",
    "    with engine.connect() as connection:\n",
    "        print(\"Connected successfully!\")\n",
    "except Exception as e:\n",
    "    print(\"Connection failed: Make sure that port forwarding step is done in the terminal.\", e)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7503e47e-12f1-44dd-8a50-786d744bbf4c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
